﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

Public Class cvDB

    Public Shared Function InsertarCV(ByVal id_tipo_documento As Integer, ByVal nro_documento As Integer, ByVal url_cv As String) As Integer
        Dim cmd As New SqlCommand("spInsertCV", DB.Conexion)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.AddWithValue("@id_tipo_documento", id_tipo_documento)
        cmd.Parameters.AddWithValue("@nro_documento", nro_documento)
        If url_cv = "" Then
            cmd.Parameters.AddWithValue("@url_cv", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@url_cv", url_cv)
        End If
        cmd.Parameters.Add("@Id_cv", SqlDbType.Int).Direction = ParameterDirection.Output
        cmd.Parameters.Add("@Error", SqlDbType.Int).Direction = ParameterDirection.Output
        cmd.Parameters.Add("@ErrorDesc", SqlDbType.VarChar, 200).Direction = ParameterDirection.Output
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
        If cmd.Parameters("@error").Value <> 0 Then
            Throw New ApplicationException(cmd.Parameters("@ErrorDesc").Value.ToString())
        End If

        Return cmd.Parameters("@Id_cv").Value
    End Function

    Public Shared Sub InsertarItemCV(ByVal id_cv As Integer, ByVal id_estudio As Integer, ByVal descripcion_estudio As String, ByVal institucion_estudio As String, ByVal fecha_estudio As String, ByVal id_experiencia As Integer, ByVal empresa As String, ByVal fecha_desde As String, ByVal fecha_hasta As String)
        Dim cmd As New SqlCommand("spInsertarItemCV", DB.Conexion)
        cmd.CommandType = CommandType.StoredProcedure


        cmd.Parameters.AddWithValue("@id_cv", id_cv)
        If id_estudio = 0 Then
            cmd.Parameters.AddWithValue("@id_estudio", 0)
        Else
            cmd.Parameters.AddWithValue("@id_estudio", id_estudio)
        End If

        If descripcion_estudio = "" Then
            cmd.Parameters.AddWithValue("@descripcion_estudio", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@descripcion_estudio", descripcion_estudio)
        End If

        If institucion_estudio = "" Then
            cmd.Parameters.AddWithValue("@institucion_estudio", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@institucion_estudio", institucion_estudio)
        End If

        If fecha_estudio = "" Then
            cmd.Parameters.AddWithValue("@fecha_estudio", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@fecha_estudio", CDate(fecha_estudio))
        End If

        If id_experiencia = 0 Then
            cmd.Parameters.AddWithValue("@id_experiencia", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@id_experiencia", id_experiencia)
        End If

        If empresa = "" Then
            cmd.Parameters.AddWithValue("@empresa", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@empresa", empresa)
        End If

        If fecha_desde = "" Then
            cmd.Parameters.AddWithValue("@Fecha_desde", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@Fecha_desde", CDate(fecha_desde))
        End If

        If fecha_hasta = "" Then
            cmd.Parameters.AddWithValue("@Fecha_hasta", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@Fecha_hasta", CDate(fecha_hasta))
        End If

        cmd.Parameters.Add("@Error", SqlDbType.Int).Direction = ParameterDirection.Output
        cmd.Parameters.Add("@ErrorDesc", SqlDbType.VarChar, 200).Direction = ParameterDirection.Output
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
        If cmd.Parameters("@error").Value <> 0 Then
            Throw New ApplicationException(cmd.Parameters("@ErrorDesc").Value.ToString())
        End If

    End Sub

    Public Shared Function GetExperienciasCV(ByVal id_cv As Integer) As DataSet
        Dim cmd As New SqlCommand("spGetExperienciasCV", DB.Conexion)
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet


        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id_cv", id_cv)

        da.SelectCommand = cmd
        da.Fill(ds, "ExperienciasCV")
        Return ds
    End Function

    Public Shared Function GetEstudiosCV(ByVal id_cv As Integer) As DataSet
        Dim cmd As New SqlCommand("spGetEstudiosCV", DB.Conexion)
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet


        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id_cv", id_cv)

        da.SelectCommand = cmd
        da.Fill(ds, "EstudiosCV")
        Return ds
    End Function
End Class
